package com.bookstore.dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import com.bookstore.model.Category;
import com.bookstore.utils.DBConnection;
import com.mysql.jdbc.Connection;

public class CategoryDAOImpl implements CategoryDAO {

	private static final String FIND_ALL = "select * from d_category";
	private static final String FIND_BY_PARENT_ID = "select dc.*, count(dcp.product_id) as pnum from d_category dc left outer join d_category_product dcp on dc.id = dcp.cat_id where dc.parent_id = ? group by dc.id";

	@Override
	public List<Category> findAll() throws Exception {
		Connection connection = null;
		PreparedStatement statement = null;
		ResultSet resultSet = null;
		List<Category> list = new ArrayList<>();
		try {
			statement = DBConnection.getConnection().prepareStatement(FIND_ALL);
			resultSet = statement.executeQuery();
			while (resultSet.next()) {
				Category category = new Category();
				category.setDescription(resultSet.getString("description"));
				category.setEnName(resultSet.getString("en_name"));
				category.setId(resultSet.getInt("id"));
				category.setName(resultSet.getString("name"));
				category.setParentId(resultSet.getInt("parent_id"));
				list.add(category);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBConnection.close(resultSet, statement, connection);
		}
		return list;
	}

}
